package chen.web.region;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import chen.web.DataSourceProvider;

public class RegionRepository {
	
	private static JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceProvider.getDataSource());

	private static final String SQL_1 = "select prov_id, prov_name from web_region_province order by prov_order desc";
	public static Map<Integer, String> getAllProvince(){
		Map<Integer, String> map = (Map<Integer, String>) jdbcTemplate.query(SQL_1, new ResultSetExtractor<Map<Integer, String>>(){
			@Override
			public Map<Integer, String> extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				// TODO Auto-generated method stub
				Map<Integer, String> map = new LinkedHashMap<Integer, String>();
				while(rs.next()){
					map.put(rs.getInt("prov_id"), rs.getString("prov_name"));
				}
				return map;
			}
			
		});
		return map;
	}
	
	private static final String SQL_2 = "select city_id, city_name from web_region_city where prov_id = ? order by city_id asc";
	public static Map<Integer, String> getCityByProvince(int province){
		Map<Integer, String> map = (Map<Integer, String>) jdbcTemplate.query(SQL_2, new Object[]{province}, new ResultSetExtractor<Map<Integer, String>>(){
			@Override
			public Map<Integer, String> extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				// TODO Auto-generated method stub
				Map<Integer, String> map = new LinkedHashMap<Integer, String>();
				while(rs.next()){
					map.put(rs.getInt("city_id"), rs.getString("city_name"));
				}
				return map;
			}
			
		});
		return map;
	}
	
	private static final String SQL_7 = "select prov_name from web_region_province where prov_id = ?";
	public static String getProvince(int provinceId){
		if(provinceId == 0)
			return null;
		String province = null;
		try{
			province = jdbcTemplate.queryForObject(SQL_7, String.class, provinceId);	
		}catch(EmptyResultDataAccessException e){
			// TODO Auto-generated catch block
			//do nothing
		}
		return province;
	}
	
	private static final String SQL_8 = "select city_name from web_region_city where city_id = ?";
	public static String getCity(int cityId){
		if(cityId == 0)
			return null;
		String city = null;
		try{
			city = jdbcTemplate.queryForObject(SQL_8, String.class, cityId);
		}catch(EmptyResultDataAccessException e){
			// TODO Auto-generated catch block
			//do nothing
		}
		return city;
	}
	
	private static final String SQL_4 = "select prov_name, city_name from web_region_province p right join web_region_city c on p.prov_id = c.prov_id where p.prov_id = ? and city_id = ?";
	public static String getProvinceAndCityName(int province, int city){
		String name = null;
		try {
			Map<String, Object> map = jdbcTemplate.queryForMap(SQL_4, province, city);
			String provName = (String)map.get("prov_name");
			String cityName = (String)map.get("city_name");
			if(provName.equals(cityName)){
				name = provName;
			} else {
				name = new StringBuffer().append(provName)
										.append('-')
										.append(cityName)
										.toString();
			}
		}catch (EmptyResultDataAccessException e) {
			// TODO Auto-generated catch block
			//do nothing
		}
		return name;
	}
	
	private static final String SQL_5 = "select res_id, res_name from web_region_residence order by res_id asc";
	public static Map<Integer, String> getAllResidence(){
		Map<Integer, String> map = (Map<Integer, String>) jdbcTemplate.query(SQL_5, new ResultSetExtractor<Map<Integer, String>>(){
			@Override
			public Map<Integer, String> extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				// TODO Auto-generated method stub
				Map<Integer, String> map = new LinkedHashMap<Integer, String>();
				while(rs.next()){
					map.put(rs.getInt("res_id"), rs.getString("res_name"));
				}
				return map;
			}
			
		});
		return map;
	}
	
	private static final String SQL_6 = "select res_name from web_region_residence where res_id = ?";
	public static String getResidence(int id){
		if(id == 0)
			return null;
		String residence = null;
		try{
			residence = jdbcTemplate.queryForObject(SQL_6, new Object[]{id}, String.class);
		}catch(EmptyResultDataAccessException e){
			// TODO Auto-generated catch block
			//do nothing
		}
		return residence;
	}
	
}
